import json

import logging

import mysql
from mysql.connector import pooling

import settings
import requests


class DatabaseUtils:
    def __init__(self, host=settings.DM_DATABASE_HOST, username=settings.DM_DATABASE_USERNAME,
                 password=settings.DM_DATABASE_PASSWORD, database=settings.DM_DATABASE_SCHEMA):
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.conn_pool = None
        self.logger = logging.getLogger("database_utils")
        self.logger.setLevel(logging.INFO)
        # 创建日志处理器和格式化器
        # ...
        # 创建连接池
        self.conn_pool = self.create_connection_pool()

    # 创建连接池
    def create_connection_pool(self):
        return pooling.MySQLConnectionPool(
            pool_name="database_pool",
            pool_size=16,
            pool_reset_session=True,
            host=self.host,
            user=self.username,
            password=self.password,
            database=self.database
        )

    # 执行查询query
    def execute_query(self, query, params=None):
        try:
            conn = self.conn_pool.get_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.execute(query, params)
            result = cursor.fetchall()
            cursor.close()
            conn.close()
            return result
        except mysql.connector.Error as e:
            self.logger.error(f"Error executing query: {e}")
            return None

    # 执行更新
    def execute_update(self, query, params=None):
        try:
            conn = self.conn_pool.get_connection()
            cursor = conn.cursor()
            cursor.execute(query, params)
            conn.commit()
            affected_rows = cursor.rowcount
            cursor.close()
            conn.close()
            return affected_rows
        except mysql.connector.Error as e:
            self.logger.error(f"Error executing update: {e}")
            return -1

    def begin_transaction(self):
        conn = self.conn_pool.get_connection()
        conn.start_transaction()
        self.logger.info("Transaction started.")
        return conn

    def commit_transaction(self, conn):
        conn.commit()
        conn.close()
        self.logger.info("Transaction committed.")

    def rollback_transaction(self, conn):
        conn.rollback()
        conn.close()
        self.logger.info("Transaction rolled back.")

    # 获取配置表信息
    def get_config_table_data(self):
        self.host = settings.DM_DATABASE_HOST
        self.username = settings.DM_DATABASE_USERNAME
        self.password = settings.DM_DATABASE_PASSWORD
        self.database = settings.DM_DATABASE_SCHEMA
        query = """
        SELECT w.id, w.name as workspace_name, d.id as dialogue_id, d.name as dialgoue_name, 
        c.id as config_id, c.dialogue_config, c.faq_id, c.transfer_content
        FROM workspace w
        JOIN dialogue d ON d.workspace_id = w.id  
        JOIN dialogue_conf c ON d.id = c.dialogue_id;
        """
        return self.execute_query(query)

    # 获取坐席需要收集的标签
    def get_entity_labels(self, workspace_id):
        self.host = settings.DM_DATABASE_HOST
        self.username = settings.DM_DATABASE_USERNAME
        self.password = settings.DM_DATABASE_PASSWORD
        self.database = settings.DM_DATABASE_SCHEMA
        query = """
        SELECT nldn.id, nldn.workspace_id, nldn.node, nldn.label 
        FROM task_dialogue_config.node_label_dialogue_new nldn 
        WHERE nldn.workspace_id = {}
        """.format(workspace_id)
        return self.execute_query(query)

    # 持久化对话信息
    def update_session_info(self, session_id, session_info):
        try:
            conn = self.conn_pool.get_connection()
            cursor = conn.cursor()

            # 将session_info转换为JSON字符串
            session_info_json = json.dumps(session_info)

            # 检查数据库中是否已存在该session_id的记录
            query = "SELECT COUNT(*) FROM dm_session_info WHERE session_id = %s"
            cursor.execute(query, (session_id,))
            count = cursor.fetchone()[0]

            if count > 0:
                # 如果记录已存在，则更新对应的JSON字符串
                query = "UPDATE dm_session_info SET session_info = %s WHERE session_id = %s"
                cursor.execute(query, (session_info_json, session_id))
            else:
                # 如果记录不存在，则插入新记录
                query = "INSERT INTO dm_session_info (session_id, session_info) VALUES (%s, %s)"
                cursor.execute(query, (session_id, session_info_json))

            conn.commit()
            affected_rows = cursor.rowcount
            cursor.close()
            conn.close()
            return affected_rows
        except mysql.connector.Error as e:
            self.logger.error(f"Error updating session info: {e}")
            return -1


# 从平台接口获取case信息
def get_case_info_by_session_id(session_id):
    logger = logging.getLogger("get_case_info_by_session_id")
    try:
        url = "{}".format(settings.PLATFORM_CASE_INFO_URL) + session_id
        response = requests.get(url, timeout=0.5)
        if response.status_code == 200:
            return json.loads(response.text)
        else:
            logger.error(f"API request failed with status code: {response.status_code}")
    except requests.exceptions.RequestException as e:
        logger.error(f"API request failed with error: {str(e)}")

    return None


if __name__ == "__main__":
    database = DatabaseUtils()
    data = database.get_config_table_data()
    print(data)
    pass
    # 配置表调用示例1 有参数
    # from database_utils import DatabaseUtils
    # print(DatabaseUtils(
    #     settings.DM_DATABASE_HOST,
    #     settings.DM_DATABASE_USERNAME,
    #     settings.DM_DATABASE_PASSWORD,
    #     settings.DM_DATABASE_SCHEMA
    # ).get_config_table_data())
    # 配置表调用示例2 无参数
    # from database_utils import DatabaseUtils
    # if __name__ == "__main__":
    #     print(DatabaseUtils().get_config_table_data())

    # 从平台接口获取case信息，调用示例
    # import database_utils
    # print( database_utils.get_case_info_by_session_id("0-2186-452561866-1625294647040151552-722"))

    # 更新session_info，调用示例
    # db_utils = DatabaseUtils()
    # session_id = "your_session_id"
    # session_info = {"key1": "value1", "key2": "value2"}
    #
    # affected_rows = db_utils.update_session_info(session_id, session_info)
    # if affected_rows > 0:
    #     print("Session info updated/inserted successfully.")
    # else:
    #     print("Failed to update/insert session info.")
